 1.电商分析之--核心交易之缓慢变化维与周期性事实表中缓慢变化维
   
   缓慢变化维（SCD；Slowly Changing Dimensions）。在现实世界中，维度的属性
随着时间的流失发生缓慢的变化（缓慢是相对事实表而言，事实表数据变化的速度比
维度表快）。
   
   处理维度表的历史变化信息的问题称为处理缓慢变化维的问题，简称SCD问题。处理
缓慢变化维的方法有以下几种常见方式：
      保留原值
      直接覆盖
      增加新属性列
      快照表
      拉链表
    1).保留原始值
	维度属性值不做更改，保留原始值。
    如商品上架售卖时间：一个商品上架售卖后由于其他原因下架，后来又再次上架，此
种情况产生了多个商品上架售卖时间。如果业务重点关注的是商品首次上架售卖时间，则
采用该方式。
	2).直接覆盖
    修改维度属性为最新值，直接覆盖，不保留历史信息。
    如商品属于哪个品类：当商品品类发生变化时，直接重写为新品类。
	3).增加新属性列
    在维度表中增加新的一列，原先属性列存放上一版本的属性值，当前属性列存放当前
版本的属性值，还可以增加一列记录变化的时间。
    缺点：只能记录最后一次变化的信息。
	4).快照表
    每天保留一份全量数据。
    简单、高效。缺点是信息重复，浪费磁盘空间。
    适用范围：维表不能太大
    使用场景多，范围广；一般而言维表都不大。
	5).拉链表
    拉链表适合于：表的数据量大，而且数据会发生新增和变化，但是大部分是不变的
(数据发生变化的百分比不大)，且是缓慢变化的（如电商中用户信息表中的某些用户基
本属性不可能每天都变化)。主要目的是节省存储空间。
    适用场景：
             表的数据量大
             表中部分字段会被更新
             表中记录变量的比例不高
             需要保留历史信息
 
 2.维表拉链表应用案例
   
   1).创建表加载数据(准备工作)
   -- 用户信息
   DROP TABLE IF EXISTS test.userinfo;
CREATE TABLE test.userinfo(
userid STRING COMMENT '用户编号',
mobile STRING COMMENT '手机号码',
regdate STRING COMMENT '注册日期')
COMMENT '用户信息'
PARTITIONED BY (dt string)
row format delimited fields terminated by ',';
   
   -- 拉链表（存放用户历史信息）
   -- 拉链表不是分区表；多了两个字段start_date、end_date
   DROP TABLE IF EXISTS test.userhis;
CREATE TABLE test.userhis(
userid STRING COMMENT '用户编号',
mobile STRING COMMENT '手机号码',
regdate STRING COMMENT '注册日期',
start_date STRING,
end_date STRING)
COMMENT '用户信息拉链表'
row format delimited fields terminated by ',';

   -- 数据(/data/lagoudw/data/userinfo.dat)
001,13551111111,2020-03-01,2020-06-20
002,13561111111,2020-04-01,2020-06-20
003,13571111111,2020-05-01,2020-06-20
004,13581111111,2020-06-01,2020-06-20
002,13562222222,2020-04-01,2020-06-21
004,13582222222,2020-06-01,2020-06-21
005,13552222222,2020-06-21,2020-06-21
004,13333333333,2020-06-01,2020-06-22
005,13533333333,2020-06-21,2020-06-22
006,13733333333,2020-06-22,2020-06-22
001,13554444444,2020-03-01,2020-06-23
003,13574444444,2020-05-01,2020-06-23
005,13555554444,2020-06-21,2020-06-23
007,18600744444,2020-06-23,2020-06-23
008,18600844444,2020-06-23,2020-06-23
   
   -- 静态分区数据加载（略）
/data/lagoudw/data/userinfo0620.dat
001,13551111111,2020-03-01
002,13561111111,2020-04-01
003,13571111111,2020-05-01
004,13581111111,2020-06-01
load data local inpath '/data/lagoudw/data/userinfo0620.dat' into table 
test.userinfo partition(dt='2020-06-20');
   
   -- 动态分区数据加载：分区的值是不固定的，由输入数据确定
   -- 创建中间表(非分区表)
   drop table if exists test.tmp1;
   create table test.tmp1 as select * from test.userinfo;
   -- tmp1 非分区表，使用系统默认的字段分割符'\001'
   alter table test.tmp1 set serdeproperties('field.delim'=',');
   -- 向中间表加载数据
   load data local inpath '/data/lagoudw/data/userinfo.dat' into table test.tmp1;
   -- 从中间表向分区表加载数据
   set hive.exec.dynamic.partition.mode=nonstrict;
   insert into table test.userinfo partition(dt) select * from test.tmp1; 
   
   与动态分区相关的参数
   hive.exec.dynamic.partition
      Default Value: false prior to Hive 0.9.0; true in Hive 0.9.0 and later
      Added In: Hive 0.6.0
   Whether or not to allow dynamic partitions in DML/DDL.
   表示开启动态分区功能
   
   hive.exec.dynamic.partition.mode
      Default Value: strict
      Added In: Hive 0.6.0
   In strict mode, the user must specify at least one static partition in case the
user accidentally overwrites all partitions. In nonstrict mode all partitions are
allowed to be dynamic.
   
   Set to nonstrict to support INSERT ... VALUES, UPDATE, and DELETE transactions
(Hive 0.14.0 and later).
   strict：最少需要有一个是静态分区
   nonstrict：可以全部是动态分区
   
   hive.exec.max.dynamic.partitions
     Default Value: 1000
     Added In: Hive 0.6.0
   Maximum number of dynamic partitions allowed to be created in total.
   表示一个动态分区语句可以创建的最大动态分区个数，超出报错
   
   hive.exec.max.dynamic.partitions.pernode
      Default Value: 100
      Added In: Hive 0.6.0
   Maximum number of dynamic partitions allowed to be created in each mapper/reducer node.
   表示每个mapper / reducer可以允许创建的最大动态分区个数，默认是100，超出则会报错。
   
   hive.exec.max.created.files
      Default Value: 100000
      Added In: Hive 0.7.0
   Maximum number of HDFS files created by all mappers/reducers in a MapReduce job.
   表示一个MR job可以创建的最大文件个数，超出报错。
   2).拉链表的实现
   userinfo(分区表) => userid、mobile、regdate => 每日变更的数据(修改的+新增的)
 / 历史数据（第一天）
    userhis（拉链表）=> 多了两个字段 start_date / end_date
	
	-- 步骤：
    -- (1).userinfo初始化（2020-06-20)。获取历史数据
001,13551111111,2020-03-01,2020-06-20
002,13561111111,2020-04-01,2020-06-20
003,13571111111,2020-05-01,2020-06-20
004,13581111111,2020-06-01,2020-06-20
    -- (2).初始化拉链表（2020-06-20)。userinfo => userhis
insert overwrite table test.userhis
select userid, mobile, regdate, dt as start_date, '9999-12-31' as end_date
from test.userinfo
where dt='2020-06-20';

    --(3).次日新增数据(2020-06-21)；获取新增数据
002,13562222222,2020-04-01,2020-06-21
004,13582222222,2020-06-01,2020-06-21
005,13552222222,2020-06-21,2020-06-21

   -- (4).构建拉链表(userhis)(2020-06-21)【核心】userinfo(2020-06-21) + userhis => userhis
   -- userinfo: 新增数据
   -- userhis：历史数据
   
   -- 第一步：处理新增数据【userinfo】（处理逻辑与加载历史数据类似）
select userid, mobile, regdate, dt as start_date, '9999-12-31' as end_date
from test.userinfo
where dt='2020-06-21';
   -- 第二步：处理历史数据【userhis】（历史包括两部分：变化的、未变化的）
   -- 变化的：start_date:不变；end_date：传入日期-1
   -- 未变化的：不做处理
   
   -- 观察数据
   select A.userid, B.userid, B.mobile, B.regdate, B.start_Date,
B.end_date
from (select * from test.userinfo where dt='2020-06-21') A
right join test.userhis B
on A.userid=B.userid;
   
   -- 编写SQL，处理历史数据
   select B.userid ,
B.mobile ,
B.regdate ,
B.start_Date ,
case when B.end_date = '9999-12-31' and A.userid is not null
then date_add( '2020-06-21' , - 1 )
else B.end_date
end as end_date
from ( select * from test.userinfo where dt= '2020-06-21' ) A
right join test.userhis B
on A.userid =B.userid ;
   
   -- 最终的处理（新增+历史数据）
   insert overwrite table test.userhis
select userid, mobile, regdate, dt as start_date, '9999-12-31' as end_date
from test.userinfo
where dt='2020-06-23'

union all

select B.userid ,
B.mobile ,
B.regdate ,
B.start_Date ,
case when B.end_date = '9999-12-31' and A.userid is not null
then date_add( '2020-06-23' , - 1 )
else B.end_date
end as end_date
from ( select * from test.userinfo where dt= '2020-06-23' ) A
right join test.userhis B
on A.userid =B.userid ;
   
   -- (5).第三日新增数据 (2020-06-22) ；获取新增数据
004,13333333333,2020-06-01,2020-06-22
005,13533333333,2020-06-21,2020-06-22
006,13733333333,2020-06-22,2020-06-22
   -- (6).构建拉链表 (2020-06-22) userinfo(2020-06-22) + userhis => userhis
   
   -- (7).第四日新增数据 (2020-06-23)
001,13554444444,2020-03-01,2020-06-23
003,13574444444,2020-05-01,2020-06-23
005,13555554444,2020-06-21,2020-06-23
007,18600744444,2020-06-23,2020-06-23
008,18600844444,2020-06-23,2020-06-23
   -- (8).构建拉链表(2020-06-23)
   处理拉链表的脚本(测试脚本)：
   /data/lagoudw/data/userzipper.sh

#!/bin/bash

source /etc/profile

if [ -n "$1" ] ;then
    do_date=$1
else
    do_date=`date -d "-1 day" +%F`
fi

sql="
insert overwrite table test.userhis
select userid, mobile, regdate, dt as start_date, '9999-12-31' as end_date
from test.userinfo
where dt='$do_date'

union all

select B.userid ,
B.mobile ,
B.regdate ,
B.start_Date ,
case when B.end_date = '9999-12-31' and A.userid is not null
then date_add( '$do_date' , - 1 )
else B.end_date
end as end_date
from ( select * from test.userinfo where dt= '$do_date' ) A
right join test.userhis B
on A.userid =B.userid ;

"

hive -e "$sql"
   
   拉链表的使用：
   -- 查看拉链表中最新数据(2020-06-23以后的数据)
   select * from userhis where end_date='9999-12-31';
   -- 查看拉链表中给定日期数据("2020-06-22")
   select * from userhis where start_date <= '2020-06-22' and end_date >= '2020-06-22';
   -- 查看拉链表中给定日期数据("2020-06-21")
   select * from userhis where start_date <= '2020-06-21' and end_date >= '2020-06-21';
   -- 查看拉链表中给定日期数据("2020-06-20")
   select * from userhis where start_date <= '2020-06-20' and end_date >= '2020-06-20';
   3).拉链表的回滚
   06-20 拉链表数据(sh xxx.sh 2020-06-20；在2020-06-21日凌晨发出命令)：
001   13551111111   2020-03-01   2020-06-20   9999-12-31
002   13561111111   2020-04-01   2020-06-20   9999-12-31
003   13571111111   2020-05-01   2020-06-20   9999-12-31
004   13581111111   2020-06-01   2020-06-20   9999-12-31

001	13551111111	2020-03-01	2020-06-20	9999-12-31	2
002	13561111111	2020-04-01	2020-06-20	9999-12-31	2
003	13571111111	2020-05-01	2020-06-20	9999-12-31	2
004	13581111111	2020-06-01	2020-06-20	9999-12-31	2
   
   06-21拉链表数据(sh xxx.sh 2020-06-21)：
001   13551111111   2020-03-01   2020-06-20   9999-12-31
002   13561111111   2020-04-01   2020-06-20   2020-06-20
002   13562222222   2020-04-01   2020-06-21   9999-12-31
003   13571111111   2020-05-01   2020-06-20   9999-12-31
004   13581111111   2020-06-01   2020-06-20   2020-06-20
004   13582222222   2020-06-01   2020-06-21   9999-12-31
005   13552222222   2020-06-21   2020-06-21   9999-12-31

001	13551111111	2020-03-01	2020-06-20	9999-12-31	2
002	13561111111	2020-04-01	2020-06-20	2020-06-20	1
002	13562222222	2020-04-01	2020-06-21	9999-12-31	2
003	13571111111	2020-05-01	2020-06-20	9999-12-31	2
004	13581111111	2020-06-01	2020-06-20	2020-06-20	1
004	13582222222	2020-06-01	2020-06-21	9999-12-31	2
005	13552222222	2020-06-21	2020-06-21	9999-12-31	2
   06-22拉链表数据：
001   13551111111   2020-03-01   2020-06-20   9999-12-31
002   13561111111   2020-04-01   2020-06-20   2020-06-20
002   13562222222   2020-04-01   2020-06-21   9999-12-31
003   13571111111   2020-05-01   2020-06-20   9999-12-31
004   13581111111   2020-06-01   2020-06-20   2020-06-20
004   13582222222   2020-06-01   2020-06-21   2020-06-21
004   13333333333   2020-06-01   2020-06-22   9999-12-31
005   13552222222   2020-06-21   2020-06-21   2020-06-21
005   13533333333   2020-06-21   2020-06-22   9999-12-31
006   13733333333   2020-06-22   2020-06-22   9999-12-31

001	13551111111	2020-03-01	2020-06-20	9999-12-31	2
002	13561111111	2020-04-01	2020-06-20	2020-06-20	1
002	13562222222	2020-04-01	2020-06-21	9999-12-31	2
003	13571111111	2020-05-01	2020-06-20	9999-12-31	2
004	13581111111	2020-06-01	2020-06-20	2020-06-20	1
004	13582222222	2020-06-01	2020-06-21	2020-06-21	1
004	13333333333	2020-06-01	2020-06-22	9999-12-31	2
005	13552222222	2020-06-21	2020-06-21	2020-06-21	1
005	13533333333	2020-06-21	2020-06-22	9999-12-31	2
006	13733333333	2020-06-22	2020-06-22	9999-12-31	2
   06-23拉链表数据：
001   13551111111   2020-03-01   2020-06-20   2020-06-22
001   13554444444   2020-03-01   2020-06-23   9999-12-31
002   13561111111   2020-04-01   2020-06-20   2020-06-20
002   13562222222   2020-04-01   2020-06-21   9999-12-31
003   13571111111   2020-05-01   2020-06-20   2020-06-22
003   13574444444   2020-05-01   2020-06-23   9999-12-31
004   13581111111   2020-06-01   2020-06-20   2020-06-20
004   13582222222   2020-06-01   2020-06-21   2020-06-21
004   13333333333   2020-06-01   2020-06-22   9999-12-31
005   13552222222   2020-06-21   2020-06-21   2020-06-21
005   13533333333   2020-06-21   2020-06-22   2020-06-22
005   13555554444   2020-06-21   2020-06-23   9999-12-31
006   13733333333   2020-06-22   2020-06-22   9999-12-31
007   18600744444   2020-06-23   2020-06-23   9999-12-31
008   18600844444   2020-06-23   2020-06-23   9999-12-31
   由于种种原因需要将拉链表恢复到 rollback_date 那一天的数据。此时有：
      end_date < rollback_date，即结束日期 < 回滚日期。表示该行数据在rollback_date之前 
产生，这些数据需要原样保留
      start_date <= rollback_date <= end_date，即开始日期 <= 回滚日期 <= 结束日期。这
些数据是回滚日期之后产生的，但是需要修改。将end_date 改为 9999-12-31
      其他数据不用管
   按以上方案进行编码：
   (1).处理 end_date < rollback_date 的数据，保留
   select userid, mobile, regdate, start_date, end_date, '1' as
tag
from test.userhis
where end_date < '2020-06-22';
   (2).处理 start_date <= rollback_date <= end_date 的数据，设置end_date=9999-12-31  
   select userid, mobile, regdate, start_date, '9999-12-31' as
end_date, '2' as tag
from test.userhis
where start_date <= '2020-06-22' and end_date >= '2020-06-22';   
   (3).将前面两步的数据写入临时表tmp（拉链表）
   drop table test.tmp;
create table test.tmp as
select userid, mobile, regdate, start_date, end_date, '1' as
tag
from test.userhis
where end_date < '2020-06-21'

union all

select userid, mobile, regdate, start_date, '9999-12-31' as
end_date, '2' as tag
from test.userhis
where start_date <= '2020-06-21' and end_date >= '2020-06-21';  
   
   select * from test.tmp cluster by userid, start_date;
   (4).模拟脚本
   /data/lagoudw/data/zippertmp.sh

#!/bin/bash

source /etc/profile

if [ -n "$1" ] ;then
    do_date=$1
else
    do_date=`date -d "-1 day" +%F`
fi

sql="
drop table test.tmp;
create table test.tmp as
select userid, mobile, regdate, start_date, end_date, '1' as tag
from test.userhis
where end_date < '$do_date'

union all

select userid, mobile, regdate, start_date, '9999-12-31' as end_date, '2' as tag
from test.userhis
where start_date <= '$do_date' and end_date >= '$do_date';
"

hive -e "$sql"
    
	逐天回滚，检查数据；
	
	方案二：保存一段时间的增量数据(userinfo)，定期对拉链表做备份(如一个月做一次备份)；
如需回滚，直接在备份的拉链表上重跑增量数据。处理简单